-- The purpose of this demo is to show how different languages sort differently.
-- © Erland Sommarskog 2025.

-- This function returns all non-deprecated collations in SQL Server.
SELECT * FROM sys.fn_helpcollations()
go
-- Create a database to play in and move there.
CREATE DATABASE Latin1_General_CI_AS COLLATE Latin1_General_CI_AS
go
USE Latin1_General_CI_AS
go

-- Create a table and add some sample data.
CREATE TABLE sortwords (no     int           NOT NULL,
                        word   nvarchar(50)  NOT NULL,
                        remark nvarchar(100) NULL,
                        CONSTRAINT pk_sortwords PRIMARY KEY (no)
)

-- Note that the ordering agrees with the sort order of the collation Latin1_General_CI_AS.
INSERT sortwords(no, word, remark)
   VALUES (1,  N'Aarhus',      'Second biggest city in Denmark'),
          (2,  N'ål',          'Swedish for "eel"'),
          (3,  N'centre',      NULL),
          (4,  N'chicos',      'Spanish for "children"'),
          (5,  N'csárdás',     'Hungarian dance'),
          (6,  N'cycle',       NULL),
          (7,  N'ñandú',       'Rhea, an ostrich-like bird'),
          (8,  N'Netherlands', NULL),
          (9,  N'Önnestad',    'Village where I grew up'),
          (10, N'Osnabrück',   'City in Germany'),
          (11, N'Pécs',        'City in Hungary'),
          (12, N'zuppa',       'Italian for "soup"')
          
-- First run the statement as is, then activate the COLLATE clauses to see how
-- these languages sort the strings. 
SELECT no, word, remark
FROM   sortwords
ORDER  BY word
   -- COLLATE Traditional_Spanish_CI_AS
   -- COLLATE Hungarian_CI_AS
   -- COLLATE Finnish_Swedish_CI_AS
   -- COLLATE Danish_Greenlandic_100_CI_AS
go
SELECT no, word, remark
FROM   sortwords
WHERE  word LIKE '[A-Z]%'

SELECT no, word, remark
FROM   sortwords
WHERE  word COLLATE Danish_Greenlandic_100_CI_AS LIKE '[A-Z]%'

SELECT no, word, remark
FROM   sortwords
WHERE  REGEXP_LIKE(word, '^\p{Lu}')
